"""
学生管理类 这里对学生进行增删改查
"""
from pymysql import Connection
from student import Student

class StudentDao:

    def __init__(self):
        self.conn = None


    def connect(self):
        """
        功能：用于获取数据库连接对象的方法
        :return: 返回数据库连接对象
        """
        self.conn = Connection(
            host='localhost',
            port=3306,
            user='root',
            password='root',
            database='db04',
            autocommit=True
        )
        return self.conn

    def close(self):
        """
        功能：释放资源
        """
        if self.conn:
           try:
               self.conn.close()
           except Exception as e:
               print('数据库关闭异常',e)
           finally:
               self.conn = None

    # 析构方法 定义了在进入 with 语句块时要执行的操作，通常用于资源分配或初始化工作。
    def __enter__(self):
        # 创建数据库连接
        self.connect()
        return self


    # 与 __enter__ 方法一起实现 with 语句的功能。它主要负责资源清理和异常处理。
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
        return False

    # 根据姓名模糊查询
    def get_student_by_name(self,name):
        """
        功能：根据姓名模糊查询
        :param name: 查询的姓名
        :return: 返回符合条件的学生信息元祖
        """
        with self.conn.cursor() as cursor:
           try:
               sql = 'select * from student where name like %s'
               cursor.execute(sql, f'%{name}%')
               result = cursor.fetchall()
               return result
           except Exception as e:
                raise e

    # 根据id删除学生信息

    # 根据id修改学生信息

    def get_student_by_id(self,id):
        """
        功能：根据id查询学生信息
        :param id:  要查询的学生信息id
        :return:  返回查询到的学生信息
        """
        with self.conn.cursor() as cursor:
           try:
               sql = "select * from student where id = %s"
               cursor.execute(sql, id)
               student = cursor.fetchone()
               return student
           except Exception as e:
               raise e

    def get_all_students(self):
        """
        功能：查询所有用户信息
        :return: 返回用户元祖
        """
        with self.conn.cursor() as cursor:
           try:
               sql = 'select * from student'
               cursor.execute(sql)
               results = cursor.fetchall()
               return results
           except Exception as e:
               raise e


    def add_student(self,student):
        """
        功能：添加学生信息
        :param student: 传递的要添加的学生信息
        :return:  返回新增的id
        """
        with self.conn.cursor() as cursor:
            try:
                sql = 'insert into student (name,age,gender,address) values(%s,%s,%s,%s)'
                cursor.execute(sql, list(student))
                return cursor.lastrowid  # 自动递增的id
            except Exception as e:
                raise e # 如果发生异常主动抛出一个异常


if __name__ == '__main__':
    with StudentDao() as ss:
        while True:
            print('------------欢迎进入学生管理系统--------------')
            print('输入1添加用户')
            print('输入2查看所有用户信息')
            print('输入3根据id查看用户信息')
            print('输入4根据姓名模糊查询')
            print('输入5根据id删除用户信息')
            print('输入6修改用户信息')
            print('输入exit退出系统')
            print('-' * 40)
            num = input('请选择您要操作的项')
            if num == '1':
                print('****************欢迎进入新增用户页面***************')
                name = input('请输入用户名：')
                age = int(input('请输入用户年龄：'))
                gender = input('请输入用户性别：')
                address = input('请输入用户地址:')
                stu = Student(name,age,gender,address)
                id = ss.add_student(stu)
                if id:
                    print('用户添加成功')
                else:
                    print('用户添加失败')
            elif num == '2':
                pass
            elif num == '3':
                pass
            elif num == 'exit':
                break
            else:
                print('输入有误请重新输入')

